/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.istt.dao.UserDao;
import org.istt.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

/**
 * This class interacts with Spring and Hibernate to save and retrieve User
 * objects.
 * 
 * @author NDTHO
 */
@Repository(value = "userDao")
public class UserDaoHibernate extends BaseDaoHibernate<User, Long> implements
		UserDao {
	Log logger = LogFactory.getLog(getClass());

	@Autowired
	public UserDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}

	public List<User> getUsers() {
		return findAll();
	}

	public User getUser(Long id) {
		User user = findById(id, false);
		if (user == null) {
			throw new ObjectRetrievalFailureException(User.class, id);
		}
		return user;
	}

	public void saveUser(User user) {
		makePersistent(user);

		if (logger.isDebugEnabled()) {
			logger.debug("userId set to: " + user.getId());
		}
	}

	public void removeUser(Long id) {
		makeTransient(getUser(id));
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<User> findUserByDeptId(final Long groupId) {

		return (List<User>) hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class)
						.setFetchMode("Department", FetchMode.JOIN);
				criteria.createAlias("department", "t1"); //t1 is TBL_WORKFLOW_PROCESS
				if(groupId!=null && !"".equals(groupId)) criteria.add(Restrictions.eq("t1.id", groupId));
				return criteria.list();
			}
		});
	}
	

	@SuppressWarnings("unchecked")
	@Override
	public List<User> findByUserName(final String username) {

		return (List<User>) hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class);
				if (username != null && !"".equals(username))
					criteria.add(Restrictions.ilike("username", "" + username
							+ ""));
				return criteria.list();

			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<User> findUser(final String username, final String lastname,
			final String firstname, final Long departmentId,
			final Long positionId) {

		return (List<User>) hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class);
				if (departmentId != null) {
					criteria.setFetchMode("Department", FetchMode.JOIN);
					criteria.createAlias("department", "t1");
				}
				if (positionId != null) {
					criteria.setFetchMode("Position", FetchMode.JOIN);
					criteria.createAlias("position", "t2");
				}

				if (username != null && !"".equals(username)) {
					criteria.add(Restrictions.ilike("username", "%" + username
							+ "%"));
				}
				if (firstname != null && !"".equals(firstname)) {
					criteria.add(Restrictions.like("firstName", "%" + firstname
							+ "%"));
				}
				if (lastname != null && !"".equals(lastname)) {
					criteria.add(Restrictions.like("lastName", "%" + lastname
							+ "%"));
				}
				if (departmentId != null) {
					criteria.add(Restrictions.eq("t1.id", departmentId));
				}
				if (positionId != null) {
					criteria.add(Restrictions.eq("t2.id", positionId));
				}
				return criteria.list();

			}
		});
	}

	@Override
	public User login(final String username, final String password) {

		User user = (User) hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class);
				criteria.add(Restrictions.eq("username", username));
				criteria.add(Restrictions.eq("password", password));
				return criteria.uniqueResult();
			}
		});

		return user;
	}

	@SuppressWarnings("unchecked")
	public List<List<Object>> findUserGroupWfs(final Long workflwProcessId) {
		return (List<List<Object>>) hibernateTemplate
				.execute(new HibernateCallback() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						StringBuilder builder = new StringBuilder();
						builder.append(" SELECT {t1.*}, {t2.*} FROM TBL_USER t1  ");
						builder.append(" LEFT JOIN TBL_GROUP_WORKFLOW t2 ON t1.ID=t2.USER_ID  ");
						if (workflwProcessId != null) {
							builder.append("  WHERE t2.WORKFLOW_PROCESS_ID= :workflwProcessId ");
						}
						builder.append(" UNION  ");
						builder.append(" SELECT t1.*, '', '', '' FROM TBL_USER t1 ");
						builder.append(" EXCEPT  ");
						builder.append(" SELECT t1.*, '', '', '' FROM TBL_USER t1 ");
						builder.append(" LEFT JOIN TBL_GROUP_WORKFLOW t2 ON t1.ID=t2.USER_ID ");
						if (workflwProcessId != null) {
							builder.append("  WHERE t2.WORKFLOW_PROCESS_ID= :workflwProcessId ");
						}
						builder.append(" ORDER by t2.WORKFLOW_PROCESS_ID desc ");

						SQLQuery sql = session.createSQLQuery(
								builder.toString())
						// .addEntity("t1", FileManagement.class)
								.addEntity("t1", User.class);
						if (workflwProcessId != null) {
							sql.setLong("workflwProcessId", workflwProcessId);
						}
						return sql.list();
					}
				});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<User> findUserByGroupWfs(final Long workflwProcessId) {

		return (List<User>) hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class)
						.setFetchMode("GroupWorkflow", FetchMode.JOIN);
				criteria.createAlias("groupWorkflows", "t1"); // t1 is
																// TBL_WORKFLOW_PROCESS
				if (workflwProcessId != null && !"".equals(workflwProcessId))
					criteria.add(Restrictions.eq("t1.workflowProcess.id",
							workflwProcessId));
				return criteria.list();

			}
		});
	}

	public boolean searchUserToDelete(final Long userId) {
		return hibernateTemplate.execute(new HibernateCallback<Boolean>() {

			@Override
			public Boolean doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(User.class)
						.setFetchMode("FileManagement", FetchMode.JOIN);
				criteria.createAlias("approvers", "t1"); // t1 is
															// TBL_FILE_MANAGEMENT
				criteria.add(Restrictions.eq("t1.activeStatus", false));
				if (userId != null && !"".equals(userId))
					criteria.add(Restrictions.eq("id", userId));
				return ((Long) criteria.setProjection(Projections.rowCount())
						.uniqueResult()) == 0l;
			}
		});
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.istt.dao.UserDao#loadUsersByUsername(java.lang.String)
	 */
	@Override
	public List<User> loadUsersByUsername(final String username) {
		return hibernateTemplate.execute(new HibernateCallback<List<User>>() {
			@SuppressWarnings("unchecked")
			@Override
			public List<User> doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session.createCriteria(User.class)
						.add(Restrictions.eq("username", username)).list();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.UserDao#loadUserAuthorities(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<String> loadUserAuthorities(String username) {
		//String sql = = "select user from User as user where "+groupID+" not in elements (user.groupsInternal)";
		return hibernateTemplate.find("select g.authority from Group g left join g.users u where u.username = ?", username);
	};
}
